/**  
 * @author renbangjie renbangjie@126.com  
 * @date 2016-4-25 下午2:29:24
 * @version V1.0  
 * <b>Copyright (c)</b> 2014医信金融信息服务（上海）有限公司-版权所有<br/>
 */
package com.yjd.bi.etl.user.service;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang3.time.DateUtils;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowCallbackHandler;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.stereotype.Component;

import com.yjd.bi.comm.utils.DbNameUtils;
import com.yjd.bi.etl.comm.BaseService;
import com.yjd.bi.report.model.BiDatabaseInfoModel;
import com.yjd.bi.report.util.BiUtil;
import com.yjd.comm.baseInfo.model.CityModel;
import com.yjd.comm.baseInfo.util.CityUtil;
import com.yjd.comm.util.ArithUtil;
import com.yjd.comm.util.DateUtil;
import com.yjd.comm.util.FrameUtil;
import com.yjd.comm.util.NumberUtil;

/**
 * 登录日留存率
 * 
 * @author renbangjie renbangjie@126.com
 * @date 2016-4-25 下午2:29:24
 */
@Component("loginRateDaysService")
public class LoginRateDaysService extends BaseService {

	private static final String SQL_DELETE_RATE = "delete from " + DbNameUtils.get("yjd_login_rate_days");

	private static final String SQL_SELECT_USER = "select `regdate`, `user_id` from "+DbNameUtils.get("yjd_user")+" where `regdate` >= ? and `regdate` < ? and `regdate` != 0";

	private static final String SQL_SELECT_LOGIN_COUNT = "select a.`login_date`, count(distinct a.`user_id`) from (select  FROM_UNIXTIME(`lastlogin`, '%Y%m%d') as login_date, `user_id` FROM "+DbNameUtils.get("yjd_user_login")+" where `lastlogin` >= :start and `lastlogin` < :end and `user_id` in (:ids)) as a group by a.`login_date`";

	private static final String SQL_INSERT_RATE = "insert into "+DbNameUtils.get("yjd_login_rate_days")+" values(?, ?, ?, ?,?)";

	private static final String SQL_SELECT_USER_CONDITION = "select `regdate`, `user_id` from "+DbNameUtils.get("yjd_user")+" a where `regdate` >= ? and `regdate` < ? and `regdate` != 0 AND exists(select 1 from "+DbNameUtils.get("yjd_user_idcard")+" b WHERE sex= ? and a.user_id=b.user_id)";

	private static final String SQL_SELECT_LOCATION_CONDITION = "select `regdate`, `user_id` from "+DbNameUtils.get("yjd_user")+" a where `regdate` >= ? and `regdate` < ? and `regdate` != 0 AND exists(select 1 from "+DbNameUtils.get("yjd_user_idcard")+" b WHERE concat(left(idcard,3),'000')= ? and a.user_id=b.user_id)";

	private static final Integer DAY_COUNT = 31;

	/**
	 * 统计
	 * 
	 * @throws Exception
	 *             void
	 */
	public void execute() throws Exception {
		BiDatabaseInfoModel model = this.getBiDatabaseInfoModel();
		JdbcTemplate jt = BiUtil.getJdbcTemplate(model);
		// 删除旧的数据
		jt.execute(SQL_DELETE_RATE);
		exc(model, 0);
		exc(model, 1);
		exc(model, 2);
		for (CityModel c : CityUtil.getProvinceCitys()) {
			exc(model, c.getId());
		}
	}

	/**
	 * 统计(条件)
	 * 
	 * @throws Exception
	 *             void
	 */
	public void exc(BiDatabaseInfoModel model, Integer condition) throws Exception {
		JdbcTemplate jt = BiUtil.getJdbcTemplate(model);
		// 计算日期范围
		Date currentDate = DateUtils.truncate(new Date(), Calendar.DAY_OF_MONTH);
		Date startDate = DateUtils.addDays(currentDate, -DAY_COUNT);

		// 查询某个时间段的所有的用户,按照注册时间进行分组
		long startTime = FrameUtil.getTime(startDate), endTime = FrameUtil.getTime(currentDate);
		Map<String, List<Integer>> signUpMap = null;
		if (condition.intValue() > 2) {
			signUpMap = getUserMapWithLocationCondition(jt, startTime, endTime,condition);
		} else {
			if (condition.intValue() == 0) {
				signUpMap = getUserMap(jt, startTime, endTime);
			} else {
				signUpMap = getUserMapWithSexCondition(jt, startTime, endTime,condition);
			}
		}

		// 每一天的登录次数
		Map<String, Integer> loginCountMap = null;
		List<Object[]> resultList = new ArrayList<Object[]>();
		String dateAfter = null;
		String signUpDate = null;
		Double signUpCount = 0.00;
		Double signInCount = 0.00;
		Double rate = 0.00;
		for (int i = 0; i < DAY_COUNT; i++) {
			signUpCount = 0.00;
			signUpDate = DateUtil.format(DateUtils.addDays(startDate, i), DateUtil.PATTERN_yyyyMMdd);
			// 注册用户数
			if (signUpMap != null && signUpMap.get(signUpDate) != null) {
				signUpCount = Double.valueOf(signUpMap.get(signUpDate).size());
			}

			// 要有注册用户
			if (signUpCount > 0) {
				loginCountMap = getUserLoginCount(jt, startTime, endTime, signUpMap.get(signUpDate));
			}

			// 每个注册日期向后推7天
			for (int j = 1; j < DAY_COUNT; j++) {
				signInCount = rate = 0.00;
				// 加一天
				dateAfter = DateUtil.format(
						DateUtils.addDays(DateUtil.parseDate(signUpDate, DateUtil.PATTERN_yyyyMMdd), j),
						DateUtil.PATTERN_yyyyMMdd);
				// 当日登录次数
				if (loginCountMap != null && loginCountMap.get(dateAfter) != null) {
					signInCount = Double.valueOf(loginCountMap.get(dateAfter));
				}
				// 留存率
				if (signUpCount > 0) {
					rate = ArithUtil.div(signInCount, signUpCount);
				}

				resultList.add(new Object[] { signUpDate, dateAfter, signUpCount,
						NumberUtil.formatNumber1(ArithUtil.mul(rate, 100)), condition });
			}
		}

		// 批量插入
		jt.batchUpdate(SQL_INSERT_RATE, resultList);
	}

	/**
	 * 用户登录数
	 * 
	 * @param jt
	 * @return List<Map<String,Integer>>
	 */
	private Map<String, Integer> getUserLoginCount(JdbcTemplate jt, Object... args) {
		MapSqlParameterSource paramSource = new MapSqlParameterSource();
		paramSource.addValue("start", args[0]);
		paramSource.addValue("end", args[1]);
		paramSource.addValue("ids", args[2]);
		final Map<String, Integer> resultMap = new HashMap<String, Integer>();
		new NamedParameterJdbcTemplate(jt).query(SQL_SELECT_LOGIN_COUNT, paramSource, new RowCallbackHandler() {
			@Override
			public void processRow(ResultSet rs) throws SQLException {
				resultMap.put(rs.getString(1), rs.getInt(2));
			}
		});

		return resultMap;
	}

	/**
	 * 获取用户id
	 * 
	 * @param jt
	 * @return Map<String,List<Integer>>
	 */
	private Map<String, List<Integer>> getUserMap(JdbcTemplate jt, Object... args) {
		final Map<String, List<Integer>> listMap = new HashMap<String, List<Integer>>();
		jt.query(SQL_SELECT_USER, new RowCallbackHandler() {
			@Override
			public void processRow(ResultSet rs) throws SQLException {
				String key = DateUtil.format(DateUtil.timestampToDate(rs.getLong(1)), DateUtil.PATTERN_yyyyMMdd);
				if (!listMap.containsKey(key)) {
					listMap.put(key, new ArrayList<Integer>());
				}
				listMap.get(key).add(rs.getInt(2));
			}
		}, args);
		return listMap;
	}

	/**
	 * 获取用户id
	 * 
	 * @param jt
	 * @return Map<String,List<Integer>>
	 */
	private Map<String, List<Integer>> getUserMapWithSexCondition(JdbcTemplate jt, Object... args) {
		final Map<String, List<Integer>> listMap = new HashMap<String, List<Integer>>();
		jt.query(SQL_SELECT_USER_CONDITION, new RowCallbackHandler() {
			@Override
			public void processRow(ResultSet rs) throws SQLException {
				String key = DateUtil.format(DateUtil.timestampToDate(rs.getLong(1)), DateUtil.PATTERN_yyyyMMdd);
				if (!listMap.containsKey(key)) {
					listMap.put(key, new ArrayList<Integer>());
				}
				listMap.get(key).add(rs.getInt(2));
			}
		}, args);
		return listMap;
	}

	/**
	 * 获取用户id
	 * 
	 * @param jt
	 * @return Map<String,List<Integer>>
	 */
	private Map<String, List<Integer>> getUserMapWithLocationCondition(JdbcTemplate jt, Object... args) {
		final Map<String, List<Integer>> listMap = new HashMap<String, List<Integer>>();
		jt.query(SQL_SELECT_LOCATION_CONDITION, new RowCallbackHandler() {
			@Override
			public void processRow(ResultSet rs) throws SQLException {
				String key = DateUtil.format(DateUtil.timestampToDate(rs.getLong(1)), DateUtil.PATTERN_yyyyMMdd);
				if (!listMap.containsKey(key)) {
					listMap.put(key, new ArrayList<Integer>());
				}
				listMap.get(key).add(rs.getInt(2));
			}
		}, args);
		return listMap;
	}
}
